--
-- PostgreSQL database cluster dump
--

-- Started on 2011-10-17 23:01:16

\connect postgres

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET escape_string_warning = off;

--
-- Roles
--

CREATE ROLE sist_biblioteca;
ALTER ROLE sist_biblioteca WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN PASSWORD 'md570a775039822ff81f40435f01e1bbb68' VALID UNTIL 'infinity';
CREATE ROLE user_biblioteca;
ALTER ROLE user_biblioteca WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN VALID UNTIL 'infinity';


--
-- Role memberships
--

GRANT user_biblioteca TO sist_biblioteca GRANTED BY postgres;




--
-- Database creation
--

REVOKE ALL ON DATABASE template1 FROM PUBLIC;
REVOKE ALL ON DATABASE template1 FROM postgres;
GRANT ALL ON DATABASE template1 TO postgres;
GRANT CONNECT ON DATABASE template1 TO PUBLIC;


\connect "Biblioteca"

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.0.4
-- Dumped by pg_dump version 9.0.4
-- Started on 2011-10-17 23:01:16

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 332 (class 2612 OID 11574)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1524 (class 1259 OID 17457)
-- Dependencies: 6
-- Name: cd; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE cd (
    id integer NOT NULL,
    id_material integer NOT NULL,
    faixas character varying(300) NOT NULL,
    ano_publicacao character varying(10) NOT NULL,
    autores character varying(300) NOT NULL
);


ALTER TABLE public.cd OWNER TO postgres;

--
-- TOC entry 1525 (class 1259 OID 17463)
-- Dependencies: 1524 6
-- Name: cd_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE cd_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.cd_id_seq OWNER TO postgres;

--
-- TOC entry 1869 (class 0 OID 0)
-- Dependencies: 1525
-- Name: cd_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE cd_id_seq OWNED BY cd.id;


--
-- TOC entry 1870 (class 0 OID 0)
-- Dependencies: 1525
-- Name: cd_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('cd_id_seq', 3, true);


--
-- TOC entry 1526 (class 1259 OID 17465)
-- Dependencies: 6
-- Name: dvd; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE dvd (
    id integer NOT NULL,
    id_material integer NOT NULL,
    regiao character varying(100) NOT NULL,
    ano_publicacao character varying(10) NOT NULL,
    autores character varying(300) NOT NULL
);


ALTER TABLE public.dvd OWNER TO postgres;

--
-- TOC entry 1527 (class 1259 OID 17468)
-- Dependencies: 6 1526
-- Name: dvd_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE dvd_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.dvd_id_seq OWNER TO postgres;

--
-- TOC entry 1873 (class 0 OID 0)
-- Dependencies: 1527
-- Name: dvd_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE dvd_id_seq OWNED BY dvd.id;


--
-- TOC entry 1874 (class 0 OID 0)
-- Dependencies: 1527
-- Name: dvd_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('dvd_id_seq', 2, true);


--
-- TOC entry 1528 (class 1259 OID 17470)
-- Dependencies: 6
-- Name: emprestimo; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE emprestimo (
    id_exemplar integer NOT NULL,
    id_usuario integer NOT NULL,
    data_emprestimo date NOT NULL,
    data_devolucao date NOT NULL
);


ALTER TABLE public.emprestimo OWNER TO postgres;

--
-- TOC entry 1529 (class 1259 OID 17473)
-- Dependencies: 1819 6
-- Name: itens; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE itens (
    id integer NOT NULL,
    id_material integer NOT NULL,
    emprestado boolean DEFAULT false NOT NULL
);


ALTER TABLE public.itens OWNER TO postgres;

--
-- TOC entry 1530 (class 1259 OID 17477)
-- Dependencies: 6 1529
-- Name: exemplar_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE exemplar_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.exemplar_id_seq OWNER TO postgres;

--
-- TOC entry 1878 (class 0 OID 0)
-- Dependencies: 1530
-- Name: exemplar_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE exemplar_id_seq OWNED BY itens.id;


--
-- TOC entry 1879 (class 0 OID 0)
-- Dependencies: 1530
-- Name: exemplar_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('exemplar_id_seq', 19, true);


--
-- TOC entry 1531 (class 1259 OID 17479)
-- Dependencies: 6
-- Name: livro; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE livro (
    id integer NOT NULL,
    id_material integer NOT NULL,
    editora character varying(100) NOT NULL,
    edicao integer NOT NULL,
    ano_publicacao character varying(10) NOT NULL,
    autores character varying(300) NOT NULL
);


ALTER TABLE public.livro OWNER TO postgres;

--
-- TOC entry 1532 (class 1259 OID 17482)
-- Dependencies: 1531 6
-- Name: livro_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE livro_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.livro_id_seq OWNER TO postgres;

--
-- TOC entry 1882 (class 0 OID 0)
-- Dependencies: 1532
-- Name: livro_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE livro_id_seq OWNED BY livro.id;


--
-- TOC entry 1883 (class 0 OID 0)
-- Dependencies: 1532
-- Name: livro_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('livro_id_seq', 2, true);


--
-- TOC entry 1533 (class 1259 OID 17484)
-- Dependencies: 1822 1823 6
-- Name: material; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE material (
    id integer NOT NULL,
    emprestado boolean DEFAULT false NOT NULL,
    num_reservas bigint DEFAULT 0 NOT NULL,
    titulo character varying(100) NOT NULL,
    tipo character varying(100) NOT NULL
);


ALTER TABLE public.material OWNER TO postgres;

--
-- TOC entry 1534 (class 1259 OID 17489)
-- Dependencies: 1533 6
-- Name: material_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE material_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.material_id_seq OWNER TO postgres;

--
-- TOC entry 1886 (class 0 OID 0)
-- Dependencies: 1534
-- Name: material_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE material_id_seq OWNED BY material.id;


--
-- TOC entry 1887 (class 0 OID 0)
-- Dependencies: 1534
-- Name: material_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('material_id_seq', 1, true);


--
-- TOC entry 1535 (class 1259 OID 17491)
-- Dependencies: 6
-- Name: reserva; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE reserva (
    id_material integer NOT NULL,
    id_usuario integer NOT NULL,
    data_reserva date
);


ALTER TABLE public.reserva OWNER TO postgres;

--
-- TOC entry 1536 (class 1259 OID 17494)
-- Dependencies: 6
-- Name: revista; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE revista (
    id integer NOT NULL,
    id_material integer NOT NULL,
    edicao integer NOT NULL,
    ano_publicacao character varying(10) NOT NULL,
    mes_publicacao character varying(30) NOT NULL
);


ALTER TABLE public.revista OWNER TO postgres;

--
-- TOC entry 1537 (class 1259 OID 17497)
-- Dependencies: 1536 6
-- Name: revista_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE revista_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.revista_id_seq OWNER TO postgres;

--
-- TOC entry 1891 (class 0 OID 0)
-- Dependencies: 1537
-- Name: revista_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE revista_id_seq OWNED BY revista.id;


--
-- TOC entry 1892 (class 0 OID 0)
-- Dependencies: 1537
-- Name: revista_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('revista_id_seq', 3, true);


--
-- TOC entry 1538 (class 1259 OID 17499)
-- Dependencies: 6
-- Name: usuario; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE usuario (
    id integer NOT NULL,
    tipo character varying(100) NOT NULL,
    nome character varying(100) NOT NULL,
    email character varying(100) NOT NULL,
    conta character varying(100) NOT NULL,
    senha character varying(100) NOT NULL,
    tempo_emprestimo integer NOT NULL,
    limite_materiais integer NOT NULL
);


ALTER TABLE public.usuario OWNER TO postgres;

--
-- TOC entry 1539 (class 1259 OID 17505)
-- Dependencies: 6 1538
-- Name: usuario_id_seq; Type: SEQUENCE; Schema: public; Owner: postgres
--

CREATE SEQUENCE usuario_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER TABLE public.usuario_id_seq OWNER TO postgres;

--
-- TOC entry 1895 (class 0 OID 0)
-- Dependencies: 1539
-- Name: usuario_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: postgres
--

ALTER SEQUENCE usuario_id_seq OWNED BY usuario.id;


--
-- TOC entry 1896 (class 0 OID 0)
-- Dependencies: 1539
-- Name: usuario_id_seq; Type: SEQUENCE SET; Schema: public; Owner: postgres
--

SELECT pg_catalog.setval('usuario_id_seq', 100, true);


--
-- TOC entry 1817 (class 2604 OID 17507)
-- Dependencies: 1525 1524
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE cd ALTER COLUMN id SET DEFAULT nextval('cd_id_seq'::regclass);


--
-- TOC entry 1818 (class 2604 OID 17508)
-- Dependencies: 1527 1526
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE dvd ALTER COLUMN id SET DEFAULT nextval('dvd_id_seq'::regclass);


--
-- TOC entry 1820 (class 2604 OID 17509)
-- Dependencies: 1530 1529
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE itens ALTER COLUMN id SET DEFAULT nextval('exemplar_id_seq'::regclass);


--
-- TOC entry 1821 (class 2604 OID 17510)
-- Dependencies: 1532 1531
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE livro ALTER COLUMN id SET DEFAULT nextval('livro_id_seq'::regclass);


--
-- TOC entry 1824 (class 2604 OID 17511)
-- Dependencies: 1534 1533
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE material ALTER COLUMN id SET DEFAULT nextval('material_id_seq'::regclass);


--
-- TOC entry 1825 (class 2604 OID 17512)
-- Dependencies: 1537 1536
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE revista ALTER COLUMN id SET DEFAULT nextval('revista_id_seq'::regclass);


--
-- TOC entry 1826 (class 2604 OID 17513)
-- Dependencies: 1539 1538
-- Name: id; Type: DEFAULT; Schema: public; Owner: postgres
--

ALTER TABLE usuario ALTER COLUMN id SET DEFAULT nextval('usuario_id_seq'::regclass);


--
-- TOC entry 1854 (class 0 OID 17457)
-- Dependencies: 1524
-- Data for Name: cd; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY cd (id, id_material, faixas, ano_publicacao, autores) FROM stdin;
1	327	Bad Romance;Alejandro;Monster	2010	Lady Gaga
2	386	Eu sei;Nesse Lugar	2011	Fresno
3	394	Naturally;Intuition;Spot Light;Rock God	2010	Selena Gomez
\.


--
-- TOC entry 1855 (class 0 OID 17465)
-- Dependencies: 1526
-- Data for Name: dvd; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY dvd (id, id_material, regiao, ano_publicacao, autores) FROM stdin;
1	444	PAL	2009	George Clooney;Vera Farmiga
2	490	PAL	2010	Sam Worthington;Liam Neeson;Luke Evans
\.


--
-- TOC entry 1856 (class 0 OID 17470)
-- Dependencies: 1528
-- Data for Name: emprestimo; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY emprestimo (id_exemplar, id_usuario, data_emprestimo, data_devolucao) FROM stdin;
\.


--
-- TOC entry 1857 (class 0 OID 17473)
-- Dependencies: 1529
-- Data for Name: itens; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY itens (id, id_material, emprestado) FROM stdin;
1	100	f
2	100	f
3	101	f
4	210	f
5	211	f
6	245	f
7	245	f
8	327	f
9	327	f
10	327	f
11	327	f
12	386	f
13	394	f
14	394	f
15	394	f
16	444	f
17	444	f
18	490	f
\.


--
-- TOC entry 1858 (class 0 OID 17479)
-- Dependencies: 1531
-- Data for Name: livro; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY livro (id, id_material, editora, edicao, ano_publicacao, autores) FROM stdin;
1	100	Pearson	8	2007	Ian Sommerville
2	101	Campus	1	2004	Peter Norvig;Stuart Russel
\.


--
-- TOC entry 1859 (class 0 OID 17484)
-- Dependencies: 1533
-- Data for Name: material; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY material (id, emprestado, num_reservas, titulo, tipo) FROM stdin;
100	f	0	Engenharia de Software	Livro
101	f	0	Inteligencia Artificial	Livro
210	f	0	IEEE Transactions on Software Engineering	Revista
211	f	0	IEEE Transactions on Software Engineering	Revista
245	f	0	Commucations da ACM	Revista
327	f	0	The Fame - Monster	CD
386	f	0	Revanche	CD
394	f	0	A Year Without Rain	CD
444	f	0	Amor sem Escalas	DVD
490	f	0	Furia de Titas	DVD
\.


--
-- TOC entry 1860 (class 0 OID 17491)
-- Dependencies: 1535
-- Data for Name: reserva; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY reserva (id_material, id_usuario, data_reserva) FROM stdin;
\.


--
-- TOC entry 1861 (class 0 OID 17494)
-- Dependencies: 1536
-- Data for Name: revista; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY revista (id, id_material, edicao, ano_publicacao, mes_publicacao) FROM stdin;
1	210	53	2006	Mar
2	211	54	2007	Set
3	245	46	2003	Jan
\.


--
-- TOC entry 1862 (class 0 OID 17499)
-- Dependencies: 1538
-- Data for Name: usuario; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY usuario (id, tipo, nome, email, conta, senha, tempo_emprestimo, limite_materiais) FROM stdin;
12	alunoGraduacao	Joao da Silva	joao@ime.eb.br	joao	abcd123	1	3
49	alunoPosGraduacao	Maria de Souza	maria@ime.eb.br	maria	abcd123	2	4
5	professor	Francisco Jose	francisco@ime.eb.br	francisco	abcd123	4	5
1	bibliotecario	Bibliotecario	biblioteca@ime.eb.br	bibliotecario	abcd123	0	0
\.


--
-- TOC entry 1832 (class 2606 OID 17515)
-- Dependencies: 1528 1528 1528
-- Name: PK_emprestimo; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY emprestimo
    ADD CONSTRAINT "PK_emprestimo" PRIMARY KEY (id_exemplar, id_usuario);


--
-- TOC entry 1828 (class 2606 OID 17517)
-- Dependencies: 1524 1524
-- Name: cd_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY cd
    ADD CONSTRAINT cd_pkey PRIMARY KEY (id);


--
-- TOC entry 1830 (class 2606 OID 17519)
-- Dependencies: 1526 1526
-- Name: dvd_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY dvd
    ADD CONSTRAINT dvd_pkey PRIMARY KEY (id);


--
-- TOC entry 1834 (class 2606 OID 17521)
-- Dependencies: 1529 1529
-- Name: exemplar_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY itens
    ADD CONSTRAINT exemplar_pkey PRIMARY KEY (id);


--
-- TOC entry 1836 (class 2606 OID 17523)
-- Dependencies: 1531 1531
-- Name: livro_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY livro
    ADD CONSTRAINT livro_pkey PRIMARY KEY (id);


--
-- TOC entry 1838 (class 2606 OID 17525)
-- Dependencies: 1533 1533
-- Name: material_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY material
    ADD CONSTRAINT material_pkey PRIMARY KEY (id);


--
-- TOC entry 1840 (class 2606 OID 17527)
-- Dependencies: 1535 1535 1535
-- Name: reserva_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY reserva
    ADD CONSTRAINT reserva_pkey PRIMARY KEY (id_material, id_usuario);


--
-- TOC entry 1842 (class 2606 OID 17529)
-- Dependencies: 1536 1536
-- Name: revista_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY revista
    ADD CONSTRAINT revista_pkey PRIMARY KEY (id);


--
-- TOC entry 1844 (class 2606 OID 17531)
-- Dependencies: 1538 1538
-- Name: usuario_pkey; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY usuario
    ADD CONSTRAINT usuario_pkey PRIMARY KEY (id);


--
-- TOC entry 1845 (class 2606 OID 17532)
-- Dependencies: 1837 1524 1533
-- Name: cd_id_material_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY cd
    ADD CONSTRAINT cd_id_material_fkey FOREIGN KEY (id_material) REFERENCES material(id);


--
-- TOC entry 1846 (class 2606 OID 17537)
-- Dependencies: 1526 1837 1533
-- Name: dvd_id_material_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY dvd
    ADD CONSTRAINT dvd_id_material_fkey FOREIGN KEY (id_material) REFERENCES material(id);


--
-- TOC entry 1847 (class 2606 OID 17542)
-- Dependencies: 1833 1529 1528
-- Name: emprestimo_id_exemplar_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY emprestimo
    ADD CONSTRAINT emprestimo_id_exemplar_fkey FOREIGN KEY (id_exemplar) REFERENCES itens(id);


--
-- TOC entry 1848 (class 2606 OID 17547)
-- Dependencies: 1843 1538 1528
-- Name: emprestimo_id_usuario_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY emprestimo
    ADD CONSTRAINT emprestimo_id_usuario_fkey FOREIGN KEY (id_usuario) REFERENCES usuario(id);


--
-- TOC entry 1849 (class 2606 OID 17552)
-- Dependencies: 1837 1533 1529
-- Name: exemplar_id_material_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY itens
    ADD CONSTRAINT exemplar_id_material_fkey FOREIGN KEY (id_material) REFERENCES material(id);


--
-- TOC entry 1850 (class 2606 OID 17557)
-- Dependencies: 1837 1533 1531
-- Name: livro_id_material_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY livro
    ADD CONSTRAINT livro_id_material_fkey FOREIGN KEY (id_material) REFERENCES material(id);


--
-- TOC entry 1851 (class 2606 OID 17562)
-- Dependencies: 1529 1833 1535
-- Name: reserva_id_exemplar_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY reserva
    ADD CONSTRAINT reserva_id_exemplar_fkey FOREIGN KEY (id_material) REFERENCES itens(id);


--
-- TOC entry 1852 (class 2606 OID 17567)
-- Dependencies: 1535 1538 1843
-- Name: reserva_id_usuario_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY reserva
    ADD CONSTRAINT reserva_id_usuario_fkey FOREIGN KEY (id_usuario) REFERENCES usuario(id);


--
-- TOC entry 1853 (class 2606 OID 17572)
-- Dependencies: 1837 1536 1533
-- Name: revista_id_mateial_fkey; Type: FK CONSTRAINT; Schema: public; Owner: postgres
--

ALTER TABLE ONLY revista
    ADD CONSTRAINT revista_id_mateial_fkey FOREIGN KEY (id_material) REFERENCES material(id);


--
-- TOC entry 1867 (class 0 OID 0)
-- Dependencies: 6
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- TOC entry 1868 (class 0 OID 0)
-- Dependencies: 1524
-- Name: cd; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE cd FROM PUBLIC;
REVOKE ALL ON TABLE cd FROM postgres;
GRANT ALL ON TABLE cd TO postgres;
GRANT ALL ON TABLE cd TO user_biblioteca;


--
-- TOC entry 1871 (class 0 OID 0)
-- Dependencies: 1525
-- Name: cd_id_seq; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON SEQUENCE cd_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE cd_id_seq FROM postgres;
GRANT ALL ON SEQUENCE cd_id_seq TO postgres;
GRANT ALL ON SEQUENCE cd_id_seq TO user_biblioteca;


--
-- TOC entry 1872 (class 0 OID 0)
-- Dependencies: 1526
-- Name: dvd; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE dvd FROM PUBLIC;
REVOKE ALL ON TABLE dvd FROM postgres;
GRANT ALL ON TABLE dvd TO postgres;
GRANT ALL ON TABLE dvd TO user_biblioteca;


--
-- TOC entry 1875 (class 0 OID 0)
-- Dependencies: 1527
-- Name: dvd_id_seq; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON SEQUENCE dvd_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE dvd_id_seq FROM postgres;
GRANT ALL ON SEQUENCE dvd_id_seq TO postgres;
GRANT ALL ON SEQUENCE dvd_id_seq TO user_biblioteca;


--
-- TOC entry 1876 (class 0 OID 0)
-- Dependencies: 1528
-- Name: emprestimo; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE emprestimo FROM PUBLIC;
REVOKE ALL ON TABLE emprestimo FROM postgres;
GRANT ALL ON TABLE emprestimo TO postgres;
GRANT ALL ON TABLE emprestimo TO user_biblioteca;


--
-- TOC entry 1877 (class 0 OID 0)
-- Dependencies: 1529
-- Name: itens; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE itens FROM PUBLIC;
REVOKE ALL ON TABLE itens FROM postgres;
GRANT ALL ON TABLE itens TO postgres;
GRANT ALL ON TABLE itens TO user_biblioteca;


--
-- TOC entry 1880 (class 0 OID 0)
-- Dependencies: 1530
-- Name: exemplar_id_seq; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON SEQUENCE exemplar_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE exemplar_id_seq FROM postgres;
GRANT ALL ON SEQUENCE exemplar_id_seq TO postgres;
GRANT ALL ON SEQUENCE exemplar_id_seq TO user_biblioteca;


--
-- TOC entry 1881 (class 0 OID 0)
-- Dependencies: 1531
-- Name: livro; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE livro FROM PUBLIC;
REVOKE ALL ON TABLE livro FROM postgres;
GRANT ALL ON TABLE livro TO postgres;
GRANT ALL ON TABLE livro TO user_biblioteca;


--
-- TOC entry 1884 (class 0 OID 0)
-- Dependencies: 1532
-- Name: livro_id_seq; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON SEQUENCE livro_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE livro_id_seq FROM postgres;
GRANT ALL ON SEQUENCE livro_id_seq TO postgres;
GRANT ALL ON SEQUENCE livro_id_seq TO user_biblioteca;


--
-- TOC entry 1885 (class 0 OID 0)
-- Dependencies: 1533
-- Name: material; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE material FROM PUBLIC;
REVOKE ALL ON TABLE material FROM postgres;
GRANT ALL ON TABLE material TO postgres;
GRANT ALL ON TABLE material TO user_biblioteca;


--
-- TOC entry 1888 (class 0 OID 0)
-- Dependencies: 1534
-- Name: material_id_seq; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON SEQUENCE material_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE material_id_seq FROM postgres;
GRANT ALL ON SEQUENCE material_id_seq TO postgres;
GRANT ALL ON SEQUENCE material_id_seq TO user_biblioteca;


--
-- TOC entry 1889 (class 0 OID 0)
-- Dependencies: 1535
-- Name: reserva; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE reserva FROM PUBLIC;
REVOKE ALL ON TABLE reserva FROM postgres;
GRANT ALL ON TABLE reserva TO postgres;
GRANT ALL ON TABLE reserva TO user_biblioteca;


--
-- TOC entry 1890 (class 0 OID 0)
-- Dependencies: 1536
-- Name: revista; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE revista FROM PUBLIC;
REVOKE ALL ON TABLE revista FROM postgres;
GRANT ALL ON TABLE revista TO postgres;
GRANT ALL ON TABLE revista TO user_biblioteca;


--
-- TOC entry 1893 (class 0 OID 0)
-- Dependencies: 1537
-- Name: revista_id_seq; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON SEQUENCE revista_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE revista_id_seq FROM postgres;
GRANT ALL ON SEQUENCE revista_id_seq TO postgres;
GRANT ALL ON SEQUENCE revista_id_seq TO user_biblioteca;


--
-- TOC entry 1894 (class 0 OID 0)
-- Dependencies: 1538
-- Name: usuario; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE usuario FROM PUBLIC;
REVOKE ALL ON TABLE usuario FROM postgres;
GRANT ALL ON TABLE usuario TO postgres;
GRANT ALL ON TABLE usuario TO user_biblioteca;


--
-- TOC entry 1897 (class 0 OID 0)
-- Dependencies: 1539
-- Name: usuario_id_seq; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON SEQUENCE usuario_id_seq FROM PUBLIC;
REVOKE ALL ON SEQUENCE usuario_id_seq FROM postgres;
GRANT ALL ON SEQUENCE usuario_id_seq TO postgres;
GRANT ALL ON SEQUENCE usuario_id_seq TO user_biblioteca;


-- Completed on 2011-10-17 23:01:17

--
-- PostgreSQL database dump complete
--

\connect "Biblioteca_Log"

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.0.4
-- Dumped by pg_dump version 9.0.4
-- Started on 2011-10-17 23:01:17

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 309 (class 2612 OID 11574)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1501 (class 1259 OID 17577)
-- Dependencies: 6
-- Name: log_transacao_emprestimos; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE log_transacao_emprestimos (
    id_usuario integer NOT NULL,
    id_item integer NOT NULL,
    data_emprestimo date NOT NULL,
    data_devolucao date NOT NULL,
    devolvido boolean NOT NULL
);


ALTER TABLE public.log_transacao_emprestimos OWNER TO postgres;

--
-- TOC entry 1502 (class 1259 OID 17580)
-- Dependencies: 6
-- Name: log_transacao_reserva; Type: TABLE; Schema: public; Owner: postgres; Tablespace: 
--

CREATE TABLE log_transacao_reserva (
    id_usuario integer NOT NULL,
    id_material integer NOT NULL,
    data_reserva date NOT NULL
);


ALTER TABLE public.log_transacao_reserva OWNER TO postgres;

--
-- TOC entry 1784 (class 0 OID 17577)
-- Dependencies: 1501
-- Data for Name: log_transacao_emprestimos; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY log_transacao_emprestimos (id_usuario, id_item, data_emprestimo, data_devolucao, devolvido) FROM stdin;
\.


--
-- TOC entry 1785 (class 0 OID 17580)
-- Dependencies: 1502
-- Data for Name: log_transacao_reserva; Type: TABLE DATA; Schema: public; Owner: postgres
--

COPY log_transacao_reserva (id_usuario, id_material, data_reserva) FROM stdin;
\.


--
-- TOC entry 1781 (class 2606 OID 17584)
-- Dependencies: 1501 1501 1501 1501 1501
-- Name: PK_log_operacoes; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY log_transacao_emprestimos
    ADD CONSTRAINT "PK_log_operacoes" PRIMARY KEY (id_usuario, id_item, data_emprestimo, data_devolucao);


--
-- TOC entry 1783 (class 2606 OID 17586)
-- Dependencies: 1502 1502 1502 1502
-- Name: PK_log_reservas; Type: CONSTRAINT; Schema: public; Owner: postgres; Tablespace: 
--

ALTER TABLE ONLY log_transacao_reserva
    ADD CONSTRAINT "PK_log_reservas" PRIMARY KEY (id_material, id_usuario, data_reserva);


--
-- TOC entry 1790 (class 0 OID 0)
-- Dependencies: 6
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


--
-- TOC entry 1791 (class 0 OID 0)
-- Dependencies: 1501
-- Name: log_transacao_emprestimos; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE log_transacao_emprestimos FROM PUBLIC;
REVOKE ALL ON TABLE log_transacao_emprestimos FROM postgres;
GRANT ALL ON TABLE log_transacao_emprestimos TO postgres;
GRANT ALL ON TABLE log_transacao_emprestimos TO user_biblioteca;


--
-- TOC entry 1792 (class 0 OID 0)
-- Dependencies: 1502
-- Name: log_transacao_reserva; Type: ACL; Schema: public; Owner: postgres
--

REVOKE ALL ON TABLE log_transacao_reserva FROM PUBLIC;
REVOKE ALL ON TABLE log_transacao_reserva FROM postgres;
GRANT ALL ON TABLE log_transacao_reserva TO postgres;
GRANT ALL ON TABLE log_transacao_reserva TO user_biblioteca;


-- Completed on 2011-10-17 23:01:18

--
-- PostgreSQL database dump complete
--

\connect postgres

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.0.4
-- Dumped by pg_dump version 9.0.4
-- Started on 2011-10-17 23:01:18

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 305 (class 2612 OID 11574)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

--
-- TOC entry 1778 (class 0 OID 0)
-- Dependencies: 6
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2011-10-17 23:01:19

--
-- PostgreSQL database dump complete
--

\connect template1

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.0.4
-- Dumped by pg_dump version 9.0.4
-- Started on 2011-10-17 23:01:19

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 1777 (class 1262 OID 1)
-- Dependencies: 1776
-- Name: template1; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON DATABASE template1 IS 'default template database';


--
-- TOC entry 305 (class 2612 OID 11574)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

--
-- TOC entry 1779 (class 0 OID 0)
-- Dependencies: 6
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2011-10-17 23:01:20

--
-- PostgreSQL database dump complete
--

-- Completed on 2011-10-17 23:01:20

--
-- PostgreSQL database cluster dump complete
--

